CREATE OR REPLACE FUNCTION make_user(name text, password text)
RETURNS void
AS $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_user WHERE usename = name) THEN
            EXECUTE 'CREATE ROLE "' || name || '" WITH LOGIN PASSWORD ''' || password || ''';';
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_fmdb_user(name text) 
RETURNS void
AS $$
    BEGIN
        EXECUTE 'GRANT fmdb_user TO "' || name || '"';
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_fmdb_admin(name text)
RETURNS void
AS $$
    BEGIN
        EXECUTE 'GRANT fmdb_user TO "' || name || '"';
        EXECUTE 'GRANT fmdb_admin TO "'  || name || '"';
    END;
$$ LANGUAGE plpgsql;

DO $$
    DECLARE 
        guest_name text = 'fmdb_guest';
    BEGIN
        IF EXISTS (SELECT 1 FROM pg_catalog.pg_group WHERE groname='fmdb_user') THEN
            RAISE EXCEPTION 'There is already a role for fmdb_user.';
        END IF;
        CREATE ROLE fmdb_user;
        --ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fmdb_user;

        IF EXISTS (SELECT 1 FROM pg_catalog.pg_group WHERE groname='fmdb_admin') THEN
            RAISE EXCEPTION 'There is already a role for fmdb_admin.';
        END IF;
        CREATE ROLE fmdb_admin WITH SUPERUSER NOINHERIT IN GROUP fmdb_user; -- allows creating plpython function
        --ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO fmdb_admin;
        --GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO fmdb_admin;

        PERFORM make_user('fmdb_guest', 'fmdb_guest');
        PERFORM add_fmdb_user('fmdb_guest');

        CREATE EXTENSION IF NOT EXISTS plpython3u;

        --REVOKE ALL PRIVILEGES ON SCHEMA private FROM public;
        --REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM public;
        --REVOKE CONNECT ON DATABASE fmdb FROM fmdb_user;
        --GRANT CONNECT ON DATABASE fmdb TO fmdb_user;


        --REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

        --GRANT USAGE ON SCHEMA public TO fmdb_user;
        --GRANT SELECT ON ALL TABLES IN SCHEMA public TO fmdb_user;

        --GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO fmdb_user;

        --ALTER DEFAULT PRIVILEGES FOR ROLE fmdb_user IN SCHEMA public GRANT SELECT ON TABLES TO fmdb_user;

        --GRANT ALL PRIVILEGES ON SCHEMA public TO fmdb_user;

        --GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fmdb_user;
        --GRANT USAGE ON SCHEMA public TO fmdb_user;
        --GRANT SELECT ON ALL TABLES IN SCHEMA public TO fmdb_user;
            --GRANT SELECT ON ALL TABLES IN SCHEMA public TO public;
        --GRANT SELECT ON ALL TABLES IN SCHEMA private TO fmdb_user;

        --GRANT ALL PRIVILEGES ON SCHEMA private TO fmdb_admin;
        --GRANT ALL PRIVILEGES ON SCHEMA public TO fmdb_admin;
        --GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fmdb_admin;
        --GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA private TO fmdb_admin;
    END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA private;
CREATE SCHEMA view;

REVOKE CONNECT ON DATABASE fmdb FROM PUBLIC;
GRANT CONNECT ON DATABASE fmdb TO fmdb_user;

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public to fmdb_user;

REVOKE USAGE ON SCHEMA view FROM PUBLIC;
GRANT USAGE ON SCHEMA view to fmdb_user;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fmdb_user;

REVOKE ALL ON ALL TABLES IN SCHEMA view FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA view TO fmdb_user;

SET ROLE fmdb_admin;

CREATE TABLE private.SchemaVersions (
    TIMESTAMP timestamp DEFAULT CURRENT_TIMESTAMP,
    USERNAME text DEFAULT CURRENT_TIMESTAMP,
    VERSION text,
    NOTES text NULL
);

CREATE TABLE private.sites_type (
    ManholeID text NOT NULL,
    SiteID text NULL,
    Deployment integer NULL,
    StartDateTime timestamp NOT NULL,
    EndDateTime timestamp NOT NULL,
    SSOAPFileName text NOT NULL,
    SSOAPMTime timestamp NOT NULL,
    Area float NOT NULL,
    AreaUnits text NOT NULL,
    RainGage text NOT NULL,
    Directory text NOT NULL,
    AllFlowsFileName text NOT NULL,
    AllFlowsMTime timestamp NOT NULL,
    EventNotesFileName text NULL, -- SSES sites don't have event notes
    EventNotesMTime timestamp NULL,
    RValueFileName text NOT NULL,
    rvaluemtime timestamp NULL,  -- allowing NULLs allows some sites to enter the database without RTK data
    rtkfiLeName text NULL,
    rtkmTime timestamp NULL
);

CREATE TABLE Sites (
    AnalysisID SERIAL PRIMARY KEY
) INHERITS (private.sites_type);
CREATE INDEX IX_Sites ON Sites(ManholeID, SiteID, Deployment);

CREATE VIEW view.sites AS 
    SELECT * FROM sites ORDER BY ManholeID, SiteID, Deployment;

CREATE TABLE problems (
    ManholeID text NOT NULL,
    SiteID text NULL,
    Deployment integer NULL,
    Problem text NOT NULL
);

CREATE TABLE AllFlows (
    AnalysisID int NOT NULL REFERENCES Sites,
    DateTime timestamp NOT NULL,
    LotusDate float NOT NULL,
    ObsFlow float NOT NULL,
    AvgDWF float NOT NULL,
    GWIAdjustment float NOT NULL,
    AdjustedDWF float NOT NULL,
    GWIFlow float NOT NULL,
    BWF float NOT NULL,
    IandI float NOT NULL,
    Curve1 float NOT NULL,
    Curve2 float NOT NULL,
    Curve3 float NOT NULL,
    Total float NOT NULL
);
CREATE INDEX IX_AllFlows ON AllFlows(AnalysisID, DateTime);

CREATE TABLE EventStatistics (
    AnalysisID int NOT NULL REFERENCES Sites,
    Event float NOT NULL,
    StartDateTime timestamp NOT NULL,
    EndDateTime timestamp NOT NULL,
    Flag text NULL, -- for good/bad column
    Outlier boolean NOT NULL,
    Duration float NOT NULL,
    IIVolume_in float NOT NULL, 
    RainVolume_in float NOT NULL, 
    TotalRValue float NOT NULL,
    PeakIIFlow_mgd float NOT NULL,
    PeakTotalFlow float NOT NULL,
    PeakFactor float NULL,
    Rainfall_in_15min float NOT NULL,
    ObservedFlow_mgd float NOT NULL,
    GWIFlow_mgd float NOT NULL,
    BaseWasteWaterFlow_mgd float NOT NULL,
    Rainfall_StartDateTime timestamp NOT NULL,
    Rainfall_EndDateTime timestamp NOT NULL,
    RainfallDuration_hrs float NOT NULL,
    R1 float NULL,
    T1 float NULL,
    K1 float NULL,
    R2 float NULL,
    T2 float NULL,
    K2 float NULL,
    R3 float NULL,
    T3 float NULL,
    K3 float NULL,
    PeakRainfall float NULL,
    SimVolume float NULL,
    simpeak float NULL,
    Notes text NULL    -- rtk columns are nullable, since some since may not have them
);
CREATE INDEX IX_RValue ON EventStatistics(AnalysisID, Event);

CREATE TABLE private.filesummary_type (
    ManholeID text NOT NULL,
    SiteID text NULL,
    Deployment int NULL,
    HasSSOAPFolder boolean NOT NULL,
    HasFinalFolder boolean NOT NULL,
    FilesAreAmbiguous boolean NOT NULL,
    HasRValue boolean NOT NULL,
    HasRTK boolean NOT NULL,
    HasAllFlows boolean NOT NULL,
    HasEventNotes boolean NOT NULL,
    ImportError text NULL,
    ModificationTimesConsistent boolean NULL
);

CREATE TABLE filesummary (
    ID SERIAL PRIMARY KEY
) INHERITS (private.filesummary_type);
CREATE INDEX IX_FileSummary ON FileSummary(ManholeID, SiteID, Deployment);

CREATE VIEW view.filesummary AS
    SELECT * FROM filesummary ORDER BY ManholeID, SiteID, Deployment;

CREATE OR REPLACE FUNCTION update_filesummary(private.filesummary_type) 
RETURNS void
AS $$
    DECLARE 
        sum_id int;
    BEGIN
        IF NOT EXISTS (
            SELECT 1
            FROM filesummary
            WHERE ManholeID = ($1).manholeid AND SiteID = ($1).siteid AND Deployment = ($1).deployment)
        THEN
            INSERT INTO filesummary VALUES (($1).*);
        ELSE
            UPDATE filesummary
            SET HasSSOAPFolder = ($1).hasssoapfolder,
                HasFinalFolder = ($1).hasfinalfolder,
                filesareambiguous = ($1).filesareambiguous,
                HasRValue = ($1).hasrvalue,
                HasRTK = ($1).hasrtk,
                HasAllFlows = ($1).hasallflows,
                HasEventNotes = ($1).haseventnotes,
                ImportError = ($1).importerror,
                ModificationTimesConsistent = ($1).modificationtimesconsistent
            WHERE ManholeID = ($1).manholeid AND SiteID = ($1).siteid AND Deployment = ($1).deployment;
        END IF;
    END; 
$$ LANGUAGE plpgsql
   SECURITY DEFINER;

CREATE FUNCTION get_sites(analysisid int DEFAULT NULL, 
    manholeid text DEFAULT NULL, 
    siteid text DEFAULT NULL, 
    deployment int DEFAULT NULL)
RETURNS SETOF sites
AS $$
    global analysisid
    global manholeid
    global siteid
    global deployment 
    qry = """
        SELECT * 
        FROM sites
    """

    has_where = False

    if analysisid is not None:
        qry += ' WHERE AnalysisID = %s' % (id,)
        has_where = True
        
    if manholeid is not None:
        if not has_where:
            qry += ' WHERE '
            has_where = True

        qry += "ManholeID = '%s'" % (manholeid,)

    if siteid is not None:
        if not has_where:
            qry += ' WHERE '
            has_where = True
        else:
            qry += ' AND '

        qry += "SiteID = '%s'" % (siteid,)

    if deployment is not None:
        if not has_where:
            qry += ' WHERE '
        else:
            qry += ' AND '

        qry += ' Deployment = %s' % (deployment,)

    qry += ' ORDER BY ManholeID, SiteID, Deployment'

    return plpy.execute(qry)

$$ LANGUAGE plpython3u
   SECURITY DEFINER;

CREATE FUNCTION get_filesummary(manholeid text DEFAULT NULL, siteid text DEFAULT NULL, deployment int DEFAULT NULL)
RETURNS SETOF filesummary
--RETURNS text
AS $$
    global manholeid
    global siteid
    global deployment 

    qry = """
        SELECT * 
        FROM FileSummary 
    """

    has_where = False

    if manholeid is not None:
        qry += ' WHERE '
        has_where = True

        qry += " WHERE ManholeID = '%s'" % (manholeid,)

    if siteid is not None:
        if not has_where:
            qry += ' WHERE '
            has_where = True
        else:
            qry += ' AND '

        qry += "SiteID = '%s'" % (siteid,)

    if deployment is not None:
        if not has_where:
            qry += ' WHERE '
        else:
            qry += ' AND '

        qry += ' Deployment = %s' % (deployment,)

    qry += ' ORDER BY ManholeID, SiteID, Deployment'

    #return qry
    return plpy.execute(qry)

$$ LANGUAGE plpython3u
   SECURITY DEFINER;

-- set the version number for the current schema in SchemaVersions.
-- This function should be called after installing or updating the database
CREATE FUNCTION set_schema_version(version text, notes text)
RETURNS void
AS
$$
    INSERT INTO private.SchemaVersions (version, notes)
    VALUES ($1, $2)
$$ LANGUAGE SQL
   SECURITY DEFINER;

-- return the latest schema version
CREATE FUNCTION get_schema_version(out version text) 
AS $$
    WITH latest_date AS (
        SELECT MAX(TIMESTAMP) as max_ts
        FROM private.SchemaVersions
    )
    SELECT VERSION
    FROM private.SchemaVersions
    WHERE TIMESTAMP = (SELECT max_ts FROM latest_date)
$$ LANGUAGE SQL
   SECURITY DEFINER;

-- given the manholeid, siteid, deployment, return the analysisid from the Sites table
CREATE FUNCTION get_analysis_id(manhole text, site text, deploy int, out analysisid int)
AS $$
    SELECT Sites.AnalysisID
    FROM Sites 
    WHERE ManholeID=manhole AND SiteID=site AND Deployment=deploy;
$$ LANGUAGE SQL
   SECURITY DEFINER;

CREATE FUNCTION update_analysis(private.sites_type)
RETURNS int AS $$
    DECLARE 
        id int;
    BEGIN
        SELECT AnalysisID INTO id 
        FROM Sites
        WHERE ManholeID = ($1).manholeid AND SiteID = ($1).siteid AND Deployment = ($1).deployment;

        IF id IS NULL THEN
            WITH newrow AS (
                INSERT INTO Sites VALUES ($1.*)
                RETURNING analysisid AS newid
            )
            SELECT newid INTO id
            FROM newrow;

            RETURN id;
        END IF;

        UPDATE Sites s
        SET StartDateTime = ($1).startdatetime,
            EndDateTime = ($1).enddatetime,
            SSOAPFileName = ($1).ssoapfilename,
            SSOAPMTime = ($1).ssoapmtime,
            Area = ($1).area,
            AreaUnits = ($1).areaunits,
            RainGage = ($1).raingage,
            Directory = ($1).directory,
            AllFlowsMTime = ($1).allflowsmtime,
            EventNotesFileName = ($1).eventnotesfilename,
            EventNotesMtime = ($1).eventnotesmtime,
            RValueFileName = ($1).rvaluefilename,
            RValueMTime = ($1).rvaluemtime,
            RTKFilename = ($1).rtkfilename,
            RTKMTime = ($1).rtkmtime
        WHERE AnalysisID = id;
        
        RETURN id;
    END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER;

    
CREATE FUNCTION update_eventstatistics(eventstatistics[])
RETURNS void
AS $$
    BEGIN
        CREATE LOCAL TEMPORARY TABLE new_vals ON COMMIT DROP AS
            SELECT * FROM unnest($1);

        DELETE FROM eventstatistics WHERE analysisid = ($1[1]).analysisid AND event NOT IN (SELECT event FROM new_vals);

        WITH upsert AS (
            UPDATE EventStatistics es
            SET StartDateTime = nv.StartDateTime,
                EndDateTime = nv.EndDateTime,
                Duration = nv.Duration,
                IIVolume_in = nv.IIVolume_in,
                RainVolume_in = nv.RainVolume_in,
                TotalRValue = nv.TotalRValue,
                PeakIIFlow_mgd = nv.PeakIIFlow_mgd,
                PeakTotalFlow = nv.PeakTotalFlow,
                PeakFactor = nv.PeakFactor,
                Rainfall_in_15min = nv.Rainfall_in_15min,
                ObservedFlow_mgd = nv.ObservedFlow_mgd,
                GWIFlow_mgd = nv.GWIFlow_mgd,
                BaseWasteWaterFlow_mgd = nv.BaseWasteWaterFlow_mgd,
                Rainfall_StartDateTime = nv.Rainfall_StartDateTime,
                Rainfall_EndDateTime = nv.Rainfall_EndDateTime,
                RainfallDuration_hrs = nv.RainfallDuration_hrs,
                Flag = nv.Flag,
                Outlier = nv.Outlier,
                Notes = nv.Notes,
                R1 = nv.R1,
                T1 = nv.T1,
                K1 = nv.K1,
                R2 = nv.R2,
                T2 = nv.T2,
                K2 = nv.K2,
                R3 = nv.R3,
                T3 = nv.T3,
                K3 = nv.K3,
                PeakRainfall = nv.PeakRainfall, 
                SimVolume = nv.SimVolume
            FROM new_vals nv
            WHERE es.AnalysisID = nv.AnalysisID AND es.Event = nv.Event
            RETURNING es.*
        )
        INSERT INTO EventStatistics 
            SELECT * FROM new_vals 
            WHERE NOT EXISTS (SELECT 1 FROM upsert up WHERE up.analysisid = new_vals.analysisid AND up.event = new_vals.event);
    END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER;

CREATE FUNCTION update_allflows(allflows[])
RETURNS void
AS $$
    BEGIN
        CREATE LOCAL TEMPORARY TABLE new_vals ON COMMIT DROP AS 
            SELECT * FROM unnest($1);

        DELETE FROM allflows WHERE analysisid = ($1[1]).analysisid AND datetime NOT IN (SELECT DateTime FROM new_vals);

        WITH upsert AS (
            UPDATE AllFlows af
            SET LotusDate = nv.LotusDate,
                ObsFlow = nv.ObsFlow,
                AvgDWF = nv.AvgDWF,
                GWIAdjustment = nv.GWIAdjustment,
                AdjustedDWF = nv.AdjustedDWF,
                GWIFlow = nv.GWIFlow,
                BWF = nv.BWF,
                IandI = nv.IandI,
                Curve1 = nv.Curve1,
                Curve2 = nv.Curve2,
                Curve3 = nv.Curve3,
                Total = nv.Total
            FROM new_vals nv
            WHERE af.AnalysisID = nv.AnalysisID AND af.DateTime = nv.datetime
            RETURNING af.*
        )
        INSERT INTO AllFlows
            SELECT * FROM new_vals
            WHERE NOT EXISTS (SELECT 1 FROM upsert up 
                              WHERE up.analysisid = new_vals.analysisid AND up.DateTime = new_vals.datetime);
    END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER;

CREATE FUNCTION export_allflows(
    analysisid int, 
    mindtime timestamp DEFAULT NULL, 
    maxdtime timestamp DEFAULT NULL, 
    columns text[] DEFAULT NULL) 
RETURNS SETOF allflows
AS $$
    global mindtime
    global maxdtime
    global columns

    import datetime

    dtime_fmt = '%Y-%m-%d %H:%M:%S'
    if isinstance(mindtime, str):
        try:
            mindtime = datetime.datetime.strptime(mindtime, dtime_fmt)
        except:
            exc = "Invalid datetime string specified for mindtime in export_allflows. Datetimes should be of the format " + \
                "'%Y-%m-%d %H:%M:%S."
            raise Exception(exc)

    if isinstance(maxdtime, str):
        try:
            maxdtime = datetime.datetime.strptime(maxdtime, dtime_fmt)
        except:
            exc = "Invalid datetime string specified for maxdtime in export_allflows. Datetimes should be of the format " + \
                "'%Y-%m-%d %H:%M:%S."
            raise Exception(exc)


    where_clause = 'WHERE analysisid = ' + str(analysisid)
    if mindtime:
        where_clause += " AND datetime >= '" + mindtime.strftime(dtime_fmt) + "' "

    if maxdtime:
        where_clause += " AND datetime <= " + maxdtime.strftime(dtime_fmt) + "'"

    columns = '*' if not columns else ','.join(columns)

    qry = 'SELECT ' + columns + ' FROM allflows ' + where_clause + ' ORDER BY DateTime'
    return plpy.execute(qry)
$$ LANGUAGE plpython3u
   SECURITY DEFINER;

CREATE FUNCTION export_eventstats(analysisid int, columns text[] DEFAULT NULL)
RETURNS SETOF eventstatistics
AS $$
    global columns
    columns = ','.join(columns) if columns else '*'
    qry = "SELECT " + columns + " FROM eventstatistics WHERE analysisid = " + str(analysisid)
    return plpy.execute(qry)
$$ LANGUAGE plpython3u
   SECURITY DEFINER;

--CREATE FUNCTION array_test(integer[])
--RETURNS TABLE (bacon int) AS $$
--    SELECT * FROM unnest($1);
--$$ LANGUAGE sql STABLE;

--DO $$
REVOKE CONNECT ON DATABASE fmdb FROM PUBLIC;
GRANT CONNECT ON DATABASE fmdb TO fmdb_user;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA private FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA view FROM PUBLIC;

GRANT USAGE ON SCHEMA public TO fmdb_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fmdb_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO fmdb_user;

GRANT USAGE ON SCHEMA view TO fmdb_user;
GRANT SELECT ON ALL TABLES IN SCHEMA view TO fmdb_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA view TO fmdb_user;

GRANT USAGE ON SCHEMA private TO fmdb_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA private TO fmdb_admin;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA private TO fmdb_admin;

--ALTER DEFAULT PRIVILEGES FOR ROLE fmdb_user IN SCHEMA public GRANT SELECT ON TABLES TO fmdb_user;
--$$ LANGUAGE sql;
